import pool from "@/lib/db";

const blogModule = {
  getAll: async (limit = 10, offset = 0) => {
    const sql = `SELECT * FROM blogs ORDER BY created_at DESC LIMIT ? OFFSET ?`;
    const [rows] = await pool.query(sql, [limit, offset]);
    const totalSql = `SELECT COUNT(*) as total FROM blogs`;
    const [[{ total }]] = await pool.query(totalSql);
    return { blogs: rows, total: total };
  },
  getById: async (id) => {
    const sql = `SELECT * FROM blogs WHERE id = ?`;
    const [rows] = await pool.query(sql, [id]);
    return rows[0];
  },
  getBySlug: async (slug) => {
    const sql = `SELECT * FROM blogs WHERE slug = ?`;
    const [rows] = await pool.query(sql, [slug]);
    return rows[0];
  },
  getSlugs: async () => {
    const sql = `SELECT slug FROM blogs`;
    const [rows] = await pool.query(sql);
    return rows.map(row => row.slug);
  },
  updateById: async (id, { slug, title, description, template, status, cover, sort, meta_title, meta_description }, conn = pool) => {
    const sql = `UPDATE blogs SET slug = ?, title = ?, description = ?, template = ?, status = ?, cover = ?, sort = ?, meta_title = ?, meta_description = ? WHERE id = ?`;
    const [rows] = await conn.query(sql, [slug, title, description, template, status, cover, sort, meta_title, meta_description, id]);
    return rows;
  },
  deleteById: async (id, conn = pool) => {
    const sql = `DELETE FROM blogs WHERE id = ?`;
    const [rows] = await conn.query(sql, [id]);
    return rows;
  },
  create: async ({ slug, title, description, template, status, cover, sort, meta_title, meta_description }, conn = pool) => {
    const sql = `INSERT INTO blogs (slug, title, description, template, status, cover, sort, meta_title, meta_description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const [rows] = await conn.query(sql, [slug, title, description, template, status, cover, sort, meta_title, meta_description]);
    return rows;
  }
}

export default blogModule;